package seace.dao;

import java.nio.ByteBuffer;
import java.nio.CharBuffer;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import seace.controller.ControleDeConexao;
import seace.model.Minicurso;
import seace.model.Participante;

public class MinicursoDao {
	
	private Connection connection;
	
	public MinicursoDao() throws SQLException{
		this.connection = ControleDeConexao.getConnection();
	}
	
	public List<Minicurso> getMinicursos() throws SQLException{
		/*
		 * Sql by ivanpires@gmail.com
		 */
		String sql = "SELECT * FROM minicursos ORDER BY nome ASC";
		PreparedStatement stmt = connection.prepareStatement(sql);
		
		ResultSet rs = stmt.executeQuery();
		List<Minicurso> minicursos = new ArrayList<Minicurso>();
		
		while (rs.next()) {
			Minicurso minicurso = new Minicurso();
			minicurso.setId(rs.getInt("id"));
			
			Charset utf8charset = Charset.forName("UTF-8");
			//Charset iso88591charset = Charset.forName("ISO-8859-1");
			//ByteBuffer inputBuffer = ByteBuffer.wrap(rs.getString("nome").getBytes());
			ByteBuffer inputBuffer = utf8charset.encode(rs.getString("nome"));
			CharBuffer data = utf8charset.decode(inputBuffer);
			
			minicurso.setNome(data.toString());
			
			minicursos.add(minicurso);
		}
		rs.close();
		stmt.close();
		
		return minicursos;
	}
	
	public List<Participante> getParticipantesMinicurso(Minicurso minicurso) throws SQLException{
		/*
		 * Sql by ivanpires@gmail.com; Add field cpf by benevid
		 */
		String sql = "SELECT p.nome, p.cpf, i.id AS inscricao FROM " +
				"inscritos_minicursos AS im, inscritos AS i, pessoas AS p " +
				"WHERE im.inscritos_id = i.id AND i.pessoa_cpf = p.cpf AND im.minicursos_id = ? ORDER BY p.nome ASC";
	
		PreparedStatement stmt = connection.prepareStatement(sql);
		
		stmt.setInt(1, minicurso.getId());
		
		ResultSet rs = stmt.executeQuery();
		List<Participante> participantes = new ArrayList<Participante>();
		
		while (rs.next()) {
			Participante participante = new Participante();
			participante.setInscricao(rs.getInt("inscricao"));
			participante.setNome(rs.getString("nome"));
			participante.setCpf(rs.getString("cpf"));
			
			participantes.add(participante);
		}
		rs.close();
		stmt.close();
		
		return participantes;
	
	}
	
	public void closeConection() throws SQLException {
		this.connection.close();
	}
}
